package com.pointwest.pls.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.pointwest.pls.constant.DBConstant;
import com.pointwest.pls.domain.Employee;
import com.pointwest.pls.domain.Seat;

public class SeatDAO{
	
	/**retrieve seat data from database using employeeID
	 * @param employeeID
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws IndexOutOfBoundsException
	 * @throws Exception
	 */
	public HashMap<String, String> getSeatByID(int employeeID) throws ClassNotFoundException, SQLException, Exception{
		BaseDAO baseDAO = new BaseDAO();
		ArrayList<String> params = new ArrayList<String>();
		ArrayList<String> columns = new ArrayList<String>();
		HashMap<String, String> searchList = new HashMap<String, String>();
		
		params.add(String.valueOf(employeeID));
		
		columns.add(DBConstant.COL_FIRST_NAME);
		columns.add(DBConstant.COL_LAST_NAME);
		columns.add(DBConstant.COL_SEAT_CODE);
		columns.add(DBConstant.COL_SEAT_TYPE_DESC);
		columns.add(DBConstant.COL_QUADRANT);
		columns.add(DBConstant.COL_LOCAL_NO);
		
		searchList = baseDAO.retrieveValue(DBConstant.SQL_SEARCH_SEAT_BY_ID, params, columns);
		return searchList;
	}
	
	/**retrieve quadrant map data from database using quadrant
	 * @param quadrant
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws Exception
	 */
	public ArrayList<Map<String, String>> getQuadrantMap(String quadrant, String locationID) throws ClassNotFoundException, SQLException, Exception{
		BaseDAO baseDAO = new BaseDAO();
		
		ArrayList<String> params = new ArrayList<String>();
		ArrayList<String> columns = new ArrayList<String>();
		ArrayList<Map<String, String>> seatList = new ArrayList<Map<String, String>>();	
		
		
		params.add(quadrant);
		params.add(locationID);
		
		columns.add(DBConstant.COL_SEAT_CODE);
		columns.add(DBConstant.COL_EMPLOYEE_ID);
		columns.add(DBConstant.COL_LOCAL_NO);
		columns.add(DBConstant.COL_SEAT_TYPE_DESC);
		columns.add(DBConstant.COL_COLUMN);
		columns.add(DBConstant.COL_ROW);
		
		seatList = baseDAO.retrieveListValue(DBConstant.SQL_GET_MAP_BY_QUADRANT_AND_LOC_ID, params, columns);
		return seatList;
	}
	
	/**update employee id from seat code
	 * @param employeeID
	 * @param seatCode
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * @throws Exception
	 */
	public int updateSeat(int employeeID, String seatCode) throws ClassNotFoundException, SQLException, Exception{
		BaseDAO baseDAO = new BaseDAO();
		ArrayList<String> params = new ArrayList<String>();
		int rowsChanged = 0;
		
		params.add(String.valueOf(employeeID));
		params.add(seatCode);
		
		rowsChanged = baseDAO.updateValue(DBConstant.SQL_UPDATE_SEAT, params);
		return rowsChanged;
	}
	
	public int deleteSeat(int employeeID, String seatCode) throws ClassNotFoundException, SQLException, Exception{
		BaseDAO baseDAO = new BaseDAO();
		ArrayList<String> params = new ArrayList<String>();
		int rowsChanged = 0;
		
		params.add(String.valueOf(employeeID));
		params.add(seatCode);
		
		rowsChanged = baseDAO.updateValue(DBConstant.SQL_DELETE_EMPLOYEE, params);
		return rowsChanged;
	}
	public int insertSeat(int employeeID, String seatCode, String quadrant, String locID, String seatTypeID, String col, String row) throws SQLException, Exception{
		BaseDAO baseDAO = new BaseDAO();
		ArrayList<String> params = new ArrayList<String>();
		int rowsChanged = 0;

		
		
		params.add(String.valueOf(employeeID));
		params.add(seatCode);
		params.add(quadrant);
		params.add(locID);
		params.add(seatTypeID);
		params.add(col);
		params.add(row);
		
		rowsChanged = baseDAO.updateValue(DBConstant.SQL_INSERT_EMPLOYEE_TO_SEAT, params);
		return rowsChanged;
	}
	
	public boolean checkSeatAvailability(String seatCode) throws SQLException, Exception{
		BaseDAO baseDAO = new BaseDAO();
		boolean isAvail = true;
		ArrayList<String> params = new ArrayList<String>();
		ArrayList<String> columns = new ArrayList<String>();
		HashMap<String, String> searchList = new HashMap<String, String>();
		
		params.add(seatCode);
		
		columns.add(DBConstant.COL_EMPLOYEE_ID);
		searchList = baseDAO.retrieveValue(DBConstant.SQL_CHECK_SEAT_AVAILABILITY, params, columns);
				
		if (searchList != null){
			isAvail = false;
		}
				
		return isAvail;
	}
}
